USE [ppjdb]
GO

CREATE SCHEMA [svc] AUTHORIZATION [public]
GO

-- select * from dbo.tblUIMessage

insert into dbo.tblUIMessage (UIErrCode,MsgTier2,MsgTier3)
select 201,
	'Login failed please retype credentials and repeat again.',
	'Login failed please retype credentials and repeat again.'
union all
select 202,
	'Login failed please contact with PPJ administrator.',
	'Login failed please contact with PPJ administrator.'
go
 
-- select * from dbo.tblMsgOutput

insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspValidateLogin',-1, 'SQL update error',201
union all
select 'uspValidateLogin',-2, 'parameter @szUserEmail is NULL or empty',201
union all
select 'uspValidateLogin',-3, 'parameter @szPassword is NULL or empty',201
union all
select 'uspValidateLogin',-4, 'User not found',201
union all
select 'uspValidateLogin',-5, 'User has revoke status',202
union all
select 'uspValidateLogin',-6, 'Store has revoke status',202
union all
select 'uspValidateLogin',-7, 'parameter @szPassword does not match, secure question has been return',201
go

-------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspValidateLogin]') 
AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspValidateLogin]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Validate user credentials
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/06/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, parameter @szUserEmail is NULL or empty
	@iRetCode = -3, parameter @szPassword is NULL or empty
	@iRetCode = -4, User not found
	@iRetCode = -5, User has revoke status
	@iRetCode = -6, Store has revoke status
	@iRetCode = -7, parameter @szPassword does not match, secure question has been return
  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/06/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------
	declare
		@szUserEmail [nvarchar] (512)
		,@szPassword [nvarchar] (128)
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)
		,@biUserID [bigint]
		,@szSecureQuestion [nvarchar] (512)

	select
		@szUserEmail = 'admin@ppj.com'
		,@szPassword = 'AdminPPJ'

	exec [svc].[uspValidateLogin]
		@szUserEmail
		,@szPassword
		,@iRetCode output 
		,@szMsgTier1 output   
		,@szMsgTier2 output  
		,@biUserID output 
		,@szSecureQuestion output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg]   
		,@biUserID as [UserID] 
		,@szSecureQuestion as [SecureQuestion]  
*/

CREATE PROCEDURE [svc].[uspValidateLogin]
	@szUserEmail [nvarchar] (512)
	,@szPassword [nvarchar] (128)
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
	,@biUserID [bigint] output 
	,@szSecureQuestion [nvarchar] (512) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspValidateLogin')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

declare @bRevokeStatus [bit],
	@szTmpPassword [nvarchar] (128),
	@biTmpUserID [bigint],
	@szTmpSecureQuestion [nvarchar] (512),
	@bStoreRevokeStatus [bit]

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''
	,@biUserID = 0
	,@bRevokeStatus = 1
	,@szTmpPassword = ''
	,@biTmpUserID = 0
	,@szSecureQuestion = ''
	,@bStoreRevokeStatus= 1

-- Validate parameters
if coalesce(@szUserEmail,'')=''
-- Parameter is NULL or empty
	set @iRetCode = -2
else if coalesce(@szPassword,'')=''
	set @iRetCode = -3
else
 begin
	select @biTmpUserID = u.UserID, 
		@bRevokeStatus = u.RevokeStatus,
		@szTmpPassword = u.[Password],
		@szTmpSecureQuestion = u.SecureQuestion,
		@bStoreRevokeStatus = s.RevokeStatus
		from dbo.tblUser u (nolock) 
		left join dbo.tblStore s (nolock) on s.StoreID = u.StoreID
		where UserEmail = @szUserEmail
		
	if @@ROWCOUNT = 0
	 begin
-- no user found
		set @iRetCode = -4
	 end
	else if coalesce(@bRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -5
	 end
	else if coalesce(@bStoreRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -6
	 end
	else if @szTmpPassword <> @szPassword COLLATE SQL_Latin1_General_CP1_CS_AS
	 begin
-- password does not match	 
		select @iRetCode = -7,
				 @szSecureQuestion = @szTmpSecureQuestion
	 end
	else
	 begin
		select @biUserID = @biTmpUserID

		-- do we have outer transaction?
		SET @TranCounter = @@TRANCOUNT;
		IF @TranCounter > 0
		  SAVE TRANSACTION uspValidateLogin;
		else
		  BEGIN TRANSACTION;

		update dbo.tblUser set DateLastLogin = GETDATE()
			where UserID = @biUserID

		-- commit transaction
		if @TranCounter = 0 -- no outer transaction
		  COMMIT TRANSACTION;
	 end 
 end

if @iRetCode <> 0
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = 

ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = 

ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	begin
	IF @TranCounter = 0 -- Transaction started in procedure.
	ROLLBACK TRANSACTION;
	ELSE
	IF XACT_STATE() <> -1 -- roll back to the savepoint
	ROLLBACK TRANSACTION uspValidateLogin;
	end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = @biTmpUserID

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END

GO

------------------------------------------------------------------------------
insert into [dbo].[tblLookUp](
	[LookUpShortName], 
	[LookUpSubType])
select 'MenuStatus',6
go

insert into [dbo].[tblLookUp](
	[LookUpShortName], 
	[LookUpSubType],
	[LookUpSubID])
select 'Invisible',1,6
union all
select 'Read-only',2,6
union all
select 'Editable',3,6
union all
select 'Full control',4,6
go

-- select * from dbo.vwLookUp

---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblAppMenu](
	[AppMenuID] [int] IDENTITY(1,1) NOT NULL,
	[MenuName] [nvarchar] (128) NOT NULL,
	[ParentID] [int] not null,
	[PageLocation] [varchar] (512) NOT NULL,
	[RevokeStatus] [bit] not null,
	[DefaultStatus]  [int] not null,
	[DateCreated] [datetime] NOT NULL,
	[UserCreated] [bigint] NOT NULL,
	[DateUpdated] [datetime] NULL,
	[UserUpdated] [bigint] NULL,
 CONSTRAINT [PK_tblAppMenu] PRIMARY KEY CLUSTERED 
(
	[AppMenuID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblAppMenu] ADD  DEFAULT ((0)) FOR [ParentID]
GO

ALTER TABLE [dbo].[tblAppMenu] ADD  DEFAULT ((0)) FOR [RevokeStatus]
GO

ALTER TABLE [dbo].[tblAppMenu] ADD  DEFAULT ((1)) FOR [DefaultStatus]
GO

ALTER TABLE [dbo].[tblAppMenu] ADD  DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[tblAppMenu] ADD  DEFAULT ((0)) FOR [UserCreated]
GO

---------------------------------------------------------------------------
insert into [dbo].[tblAppMenu](
	[MenuName],
	[ParentID],
	[PageLocation],
	[RevokeStatus],
	[DefaultStatus])
select 'Dashboard',0,'',0,2 -- read-only
union all	
select 'Store Info',1,'',0,2 -- read-only
union all	
select 'Domain Management',2,'',0,2 -- read-only
union all	
select 'Billing Settings',2,'',0,1 -- invisible
union all	
select 'Coupon Settings',2,'',0,2 -- invisible
union all	
select 'User Management',2,'',0,3 -- editable
union all	
select 'Invoices',2,'',0,1 -- invisible
union all	
select 'PopUpJoe Statistics',1,'',0,2 -- invisible
GO
